import requests
import json
import schedule
import time
from bs4 import BeautifulSoup
import pymysql


#数据库配置
host = '127.0.0.1'
user = 'root'
password = 'root'
db = 'jmx2023'
charset = 'utf8'
port = 3306

def get_req(page):
    req_data={
        "gameNo": "04",
        "provinceId": 0,
        "pageSize": 30,
        "isVerify": 1,
        "termLimits": 0,
        "pageNo": page
    }
    return req_data

# 连接数据库,返回连接
def get_conn():
    conn = pymysql.connect(host=host, port=port, user=user, password=password, db=db)
    return conn

#根据连接创建游标对象，返回游标对象
def get_cur(conn):
    cursor = conn.cursor()
    return cursor

# def operate_red(as_string):
#     # 使用逗号分隔字符串，将分隔后的子字符串存储在列表中  
#     numbers = as_string.split(",")  
#     # 输出分隔后的子字符串  
#     for number in numbers:  
#         print(number)


def insert(blue01,blue02,blue03,blue04,blue05,blue06,yellow,pub_time,number):
    try:
        conn=get_conn()
        cursor=get_cur(conn)
        # insert into welfare_lotto(pic_path,knowledge_id) VALUES("",(SELECT id FROM `baidu_wiki_knowledge` where key_name="半干法脱硫" and wiki_id=3))
        sql="insert into qixing_lotto(blue01,blue02,blue03,blue04,blue05,blue06,yellow,pub_time,number) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        cursor.execute(sql,(blue01,blue02,blue03,blue04,blue05,blue06,yellow,pub_time,number))
        conn.commit()
    except Exception as e:
        print(str(e))
    finally:
        cursor.close()
        conn.close()


pageNum=1
pageSize=30

while pageNum<=pageSize:
    rep=requests.get("https://webapi.sporttery.cn/gateway/lottery/getHistoryPageListV1.qry",params=get_req(pageNum))
    data=rep.json()['value']['list']
    #print(data)

    for res in data:
        pub_time=res['lotteryDrawTime']
        number=res['lotteryDrawNum']
        # yellow=data[0]['lotteryDrawTime']
        sum=res['lotteryDrawResult']
        # 使用逗号分隔字符串，将分隔后的子字符串存储在列表中  
        numbers = sum.split(" ")  
        # 输出分隔后的子字符串  
        for i in range(len(numbers)):  
            if i==0:
                blue01=numbers[i]
            if i==1:
                blue02=numbers[i]
            if i==2:
                blue03=numbers[i]
            if i==3:
                blue04=numbers[i]
            if i==4:
                blue05=numbers[i]
            if i==5:
                blue06=numbers[i]
            if i==6:
                yellow=numbers[i]

        insert(blue01,blue02,blue03,blue04,blue05,blue06,yellow,pub_time,number)

    pageNum=pageNum+1
    time.sleep(3)
